<?php

namespace yogurt\db\driver;

use yogurt\db\Builder;

class Mysql extends Builder
{


    protected $tableName = [];

    protected $distinct = '';

    protected $field = '*';

    protected $join = [];

    protected $where = '';

    protected $group = '';

    protected $having = '';

    protected $order = '';

    protected $limit = '';

    protected $union = [];

    protected $lock = '';

    protected $commit = '';

    protected $force = '';

    protected $showSql = false;

    protected $trans = 0;

    protected $comment = '';

    protected $cache = '';

    protected $alias = [];

    protected $sql = '';

    protected $count = false;


    public function __construct($config = [])
    {
        $this->config = $config;
    }


    /**
     * 获取数据库表名称，参数不带表前缀
     * @param $tableName
     * @return Mysql
     */
    public function name($tableName): Mysql
    {
        array_push($this->tableName, is_null($this->config['prefix']) ? $tableName : $this->config['prefix'] . $tableName);
        return $this;
    }

    /**
     * 获取数据库表名称，参数带表前缀
     * @param $tableName
     * @return Mysql
     */
    public function table($tableName): Mysql
    {
        array_push($this->tableName, $tableName);
        return $this;
    }

    /** where条件查询
     * @param $field
     * @param null $op
     * @param null $condition
     * @return Mysql
     */
    public function where($field, $op = null, $condition = null): Mysql
    {
        $num = func_num_args();
        $args = func_get_args();
        if (empty($this->where)) {
            $this->where = "WHERE ";
        } else {
            $this->where .= "AND ";
        }

        return $this->whereSql($num, $args);

    }


    /** whereOr条件查询
     * @param $field
     * @param null $op
     * @param null $condition
     * @return Mysql
     */
    public function whereOr($field, $op = null, $condition = null): Mysql
    {
        $num = func_num_args();
        $args = func_get_args();
        if (empty($this->where)) {
            $this->where = "WHERE ";
        } else {
            $this->where .= "OR ";
        }

        return $this->whereSql($num, $args);
    }

    /**
     * 获取where的sql
     * @param $num
     * @param $args
     * @return Mysql
     */
    public function whereSql($num, $args): Mysql
    {
        if ($num == 0) {
            die("function where at least one parameter.");
        }
        if ($num == 1) {
            foreach ($args[0] as $key => $val) {
                $val = $this->filterStr($val);
                if (trim($this->where) == "WHERE") {
                    $this->where .= "`{$key}` = {$val} ";
                } else {
                    $this->where .= "AND `{$key}` = {$val} ";
                }
            }
        }
        if ($num == 2) {
            $args[1] = $this->filterStr($args[1]);
            $this->where .= "`{$args['0']}` = {$args[1]} ";
        }
        if ($num == 3) {
            if (strtolower($args[1]) == 'null') {
                $this->where .= "`{$args['0']}` IS NULL ";
            }
            if (strtolower($args[1]) == 'not null') {
                $this->where .= "`{$args['0']}` IS NOT NULL ";
            }
            if (strtolower($args[1]) == 'in') {
                if (is_array($args[2])) {
                    $args[2] = implode(',', $args[2]);
                }
                $this->where .= "`{$args['0']}` IN({$args[2]})";
            }
            if (strtolower($args[1]) == 'not in') {
                if (is_array($args[2])) {
                    $args[2] = implode(',', $args[2]);
                }
                $this->where .= "`{$args['0']}` NOT IN({$args[2]})";
            }

            if (strtolower($args[1]) == 'between') {
                if (!is_array($args[2])) {
                    $args[2] = explode(',', $args[2]);
                }
                $this->where .= "`{$args['0']}` BETWEEN {$args[2][0]} AND {$args[2][1]}";
            }
            if (strtolower($args[1]) == 'not between') {
                if (!is_array($args[2])) {
                    $args[2] = explode(',', $args[2]);
                }
                $this->where .= "`{$args['0']}` NOT BETWEEN {$args[2][0]} AND {$args[2][1]}";
            }

            $where = [
                'like',
                'not like',
                '=',
                '<>',
                '!=',
                '>',
                '<',
                '>=',
                '<=',
                'exists',
                'not exists',
                'regexp',
                'exp'
            ];
            if (in_array(strtolower($args[1]), $where)) {
                $args[2] = $this->filterStr($args[2]);
                $fieldArr = explode('|', $args['0']);
                foreach ($fieldArr as $key => $value) {
                    if ($key == 0) {
                        $this->where .= "{$value} {$args[1]} {$args[2]} ";
                    } else {
                        $this->where .= " OR {$value} {$args[1]} {$args[2]} ";
                    }
                }
            }
        }

        return $this;
    }

    /**
     * 插入方法
     * @param array $data 字段-值的一维数组
     * @return int|void 受影响的行数
     */
    public function insert(array $data)
    {
        $data = $this->dataFormat($this->tableName[0], $data);
        if (!$data) return;
        $sql = "INSERT INTO " . $this->tableName[0] . "(" . implode(',', array_keys($data)) . ") VALUES (" . implode(',', array_values($data)) . ")";
        return $this->exec($sql);
    }

    /**
     * 删除方法
     * @param false $model
     * @return false|int 受影响的行数
     */
    public function delete($model = false)
    {
        if (!$model) {
            if (!trim($this->where)) return false;
        }
        $sql = "DELETE FROM " . $this->tableName[0] . " " . $this->where;
        return $this->exec($sql);
    }

    /**
     * 更新函数
     * @param array $data 参数数组
     * @return int|void 受影响的行数
     */
    public function update(array $data)
    {
        //安全考虑,阻止全表更新
        if (!trim($this->where)) return false;
        $data = $this->dataFormat($this->tableName[0], $data);
        if (!$data) return;
        $valArr = [];
        foreach ($data as $k => $v) {
            $valArr[] = $k . '=' . $v;
        }
        $valStr = implode(',', $valArr);
        return $this->updateSql($valStr);
    }


    /**
     * 查询多条数据函数
     * @return array 结果集
     */
    public function select()
    {
        return $this->getData(true);
    }

    /**
     * 查询单条数据函数
     * @param int $primary
     * @return array 结果集
     */
    public function find($primary = 0)
    {
        if (!empty($primary)) {
            $tableName = $this->tableName;
            $primaryKey = $this->getPrimaryKey($tableName[0]);
            $this->where = ' WHERE ' . $primaryKey . '=' . $primary[0];
            $this->tableName = $tableName;
        }
        return self::getData();
    }


    /**
     * 获取数据
     * @param false $batch
     * @param bool $reset
     * @return array|bool|\yogurt\cache\Builder
     */
    public function getData($batch = false, $reset = true)
    {
        $sql = $this->getSelectSql();
        $data = $this->query($sql, $batch, $reset);
        return $data;
    }

    /**
     * @param string $sql
     * @param bool $batch
     * @param bool $reset
     * @return mixed
     */
    public function query(string $sql, $batch = true, $reset = true)
    {
        $this->master = false;
        $this->readMaster(false);
        if ($this->showSql == true) {
            $result = $sql;
        } else {
            $pdoStmt = $this->links[$this->master]->prepare($sql); //prepare或者query 返回一个PDOStatement
            $pdoStmt->execute();
            if ($batch) {
                $result = $pdoStmt->fetchAll(\PDO::FETCH_ASSOC);
            } else {
                $result = $pdoStmt->fetch(\PDO::FETCH_ASSOC);
            }
        }
        if ($reset) {
            $this->resetParam();
        }
        return $result;
    }

    /**
     * 执行语句 针对 INSERT, UPDATE 以及DELETE,exec结果返回受影响的行数
     * @param string $sql sql指令
     * @return integer
     */
    public function exec(string $sql): int
    {
        $this->master = true;
        $this->readMaster(true);
        if ($this->showSql == true) {
            exit($sql);
        }
        $result = $this->links[$this->master]->exec($sql);
        $this->resetParam();
        return $result;
    }


    /**
     * 获取sql
     * @return string
     */
    public function getSelectSql(): string
    {
        $distinct = empty($this->distinct) ? "" : $this->distinct;
        $field = empty($this->field) ? "*" : $this->field;
        $force = empty($this->force) ? "" : $this->force;
        $group = empty($this->group) ? "" : $this->group;
        $having = empty($this->having) ? "" : $this->having;
        $order_by = empty($this->order) ? "" : $this->order;
        $limit = empty($this->limit) ? "" : $this->limit;
        $lock = empty($this->lock) ? "" : $this->lock;
        $comment = empty($this->comment) ? "" : $this->comment;

        $this->sql = "SELECT " . $distinct;
        if ($this->count) {
            $this->sql .= "COUNT(1), ";
        }
        $this->sql .= " " . $field;
        $this->sql .= " FROM ";
        // 处理join表名称
        foreach ($this->tableName as $key => $val) {
            $this->sql .= "`{$val}`";
            if (!empty($this->alias)) {
                $this->sql .= ' AS ' . $this->alias[$key];
            }

        }
        // join连接处理
        foreach ($this->join as $jk => $jv) {
            $this->sql .= ' ' . $jv['model'] . ' JOIN ' . $this->config['prefix'] . $jv['option'] . ' ON ' . $jv['where'];
        }
        $this->sql .= " " . $force;
        $this->sql .= " " . $this->where;
        $this->sql .= " " . $group;
        $this->sql .= " " . $having;
        $this->sql .= " " . $order_by;
        $this->sql .= " " . $limit;
        $this->sql .= " " . $lock;
        foreach ($this->union as $uv) {
            $this->sql .= " " . $uv;
        }
        $this->sql .= ";";
        $this->sql .= $comment . "";

        $this->sql = preg_replace("/\s(?=\s)/", "\\1", $this->sql);

        if ($this->showSql) {
            exit($this->sql);
        }

        return $this->sql;
    }

    /**
     * 查询sql语句
     * @return Mysql
     */
    public function fetchSql(): Mysql
    {
        $this->showSql = true;
        return $this;
    }


    /**
     * 过滤字符串
     * @param $str
     * @return string|int
     */
    public function filterStr($str)
    {
        if (!is_numeric($str)) {
            $str = "'" . $str . "'";
            $str = htmlspecialchars($str);
        }
        return $str;
    }

    /**
     * 执行sql语句，自动判断进行查询或者执行操作
     * @param string $sql SQL指令
     * @return array|int
     */
    private function doSql(string $sql)
    {
        $queryIps = 'INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|LOAD DATA|SELECT .* INTO|COPY|ALTER|GRANT|REVOKE|LOCK|UNLOCK';
        if (preg_match('/^\s*"?(' . $queryIps . ')\s+/i', $sql)) {
            return $this->exec($sql);
        } else {
            //查询操作
            return $this->query($sql);
        }
    }

    /**
     * 字段和表名添加 `符号
     * 保证指令中使用关键字不出错 针对mysql
     * @param string $value
     * @return string
     */
    protected function addChar(string $value): string
    {
        if ('*' == $value || false !== strpos($value, '(') || false !== strpos($value, '.') || false !== strpos($value, '`')) {
            //如果包含* 或者 使用了sql方法 则不作处理
        } elseif (false === strpos($value, '`')) {
            $value = '`' . trim($value) . '`';
        }
        return $value;
    }

    /**
     * 取得数据表的字段信息
     * @param string $tbName 表名
     * @return array
     */
    protected function tbFields(string $tbName): array
    {
        $this->master = true;
        $this->readMaster(true);
        $sql = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME="' . $tbName . '" AND TABLE_SCHEMA="' . $this->config['database'] . '"';
        $stmt = $this->links[$this->master]->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        $ret = array();
        foreach ($result as $key => $value) {
            $ret[$value['COLUMN_NAME']] = 1;
        }
        return $ret;
    }

    /**
     * 过滤并格式化数据表字段
     * @param string $tbName 数据表名
     * @param array $data 提交数据
     * @return array $neat
     */
    protected function dataFormat(string $tbName, array $data): array
    {
        if (!is_array($data)) {
            return array();
        }
        $table_column = $this->tbFields($tbName);
        $ret = array();
        foreach ($data as $key => $val) {
            if (!is_scalar($val)) continue; //值不是标量则跳过
            if (array_key_exists($key, $table_column)) {
                $key = $this->addChar($key);
                if (is_int($val)) {
                    $val = intval($val);
                } elseif (is_float($val)) {
                    $val = floatval($val);
                } elseif (preg_match('/^\(\w*(\+|\-|\*|\/)?\w*\)$/i', $val)) {
                    // 支持在字段的值里面直接使用其它字段 ,例如 (score+1) (name) 必须包含括号
                    $val = $val;
                } elseif (is_string($val)) {
                    $val = '"' . addslashes($val) . '"';
                }
                $ret[$key] = $val;
            }
        }
        return $ret;
    }

    /**
     * 获取表结构信息
     * @param $tableName
     * @return array|bool
     */
    public function getTableInfo($tableName)
    {
        return $this->query('DESC ' . $tableName);
    }

    /**
     * 获取表主键字段
     * @param $tableName
     * @return mixed|string
     */
    public function getPrimaryKey($tableName)
    {
        $tableInfo = $this->getTableInfo($tableName);
        $primaryKey = 'id';
        foreach ($tableInfo as $item) {
            if ($item['Key'] == 'PRI') {
                $primaryKey = $item['Field'];
                break;
            }
        }
        return $primaryKey;
    }

    /**
     * 公共更新方法
     * @param $set
     * @return int
     */
    protected function updateSql($set): int
    {
        $sql = "UPDATE `" . trim($this->tableName[0]) . "` SET " . trim($set) . " " . trim($this->where);
        return $this->exec($sql);
    }


    /**
     * 每次执行sql之后重置参数，避免下次重复
     */
    public function resetParam()
    {

        $this->tableName = [];

        $this->distinct = '';

        $this->field = '*';

        $this->join = [];

        $this->where = '';

        $this->group = '';

        $this->having = '';

        $this->order = '';

        $this->limit = '';

        $this->union = [];

        $this->lock = '';

        $this->commit = '';

        $this->force = '';

        $this->showSql = false;

        $this->comment = '';

        $this->cache = '';

        $this->alias = [];

        $this->sql = '';

        $this->count = false;

        $this->option = [];
    }

    /**
     * 设置查询字段
     * @param mixed $field 字段数组
     * @return $this
     */
    public function field($field): Mysql
    {
        if (is_string($field)) {
            $field = explode(',', $field);
        }
        $nField = array_map(array($this, 'addChar'), $field);
        $this->field = implode(',', $nField);
        return $this;
    }

    /**
     * 强制使用索引
     * @param string $option
     * @return Builder
     */
    public function force(string $option): Builder
    {
        $this->force = " FORCE INDEX(" . $option . ") ";
        return $this;
    }

    /**
     * 设置排序
     * @param mixed $option 排序条件数组 例:array('sort'=>'desc')
     * @return $this
     */
    public function order($option): Mysql
    {
        $this->order = ' ORDER BY ';
        if (is_string($option)) {
            $this->order .= $option;
        } elseif (is_array($option)) {
            foreach ($option as $k => $v) {
                $order = $this->addChar($k) . ' ' . $v;
                $this->order .= isset($mark) ? ',' . $order : $order;
                $mark = 1;
            }
        }
        return $this;
    }

    /**
     * 数据分组查询
     * @param $option
     * @return mixed
     */
    public function group($option)
    {
        $this->group = " GROUP BY " . $option;
        return $this;
    }

    /**
     * having过滤数据
     * @param $option
     * @return mixed
     */
    public function having($option)
    {
        $this->having = " HAVING " . $option;
        return $this;
    }

    /**
     * 设置查询行数及页数
     * @param int $page pageSize不为空时为页数，否则为行数
     * @param int $pageSize 为空则函数设定取出行数，不为空则设定取出行数及页数
     * @return $this
     */
    public function limit($page, $pageSize = null): Mysql
    {
        if ($pageSize === null) {
            $this->limit = "LIMIT " . $page;
        } else {
            $pageval = intval(($page - 1) * $pageSize);
            $this->limit = "LIMIT " . $pageval . "," . $pageSize;
        }
        return $this;
    }

    /**
     * 获取指定字段值
     * @param $field
     * @return false|mixed
     */
    public function value($field)
    {
        $result = $this->find();
        if (!empty($result)) {
            return $result[$field];
        }
        return false;
    }

    /**
     * 表名称别名
     * @param string $alias
     * @return Builder
     */
    public function alias(string $alias): Builder
    {
        array_push($this->alias, $alias);
        return $this;
    }

    /**
     * 表连接方法
     * @param string $option
     * @param string $where
     * @param string $model
     * @return Builder
     */
    public function join(string $option, string $where, $model = 'LEFT'): Builder
    {
        $joinArr = [];
        $joinArr['option'] = $option;
        $joinArr['where'] = $where;
        $joinArr['model'] = $model;
        array_push($this->join, $joinArr);
        return $this;
    }

    /**
     * 批量插入数据
     * @param array $data
     * @return int|void
     */
    public function insertAll(array $data)
    {
        if (!$data) return;
        $sql = "INSERT INTO " . $this->tableName[0];
        $insert = $this->dataFormat($this->tableName[0], $data[0]);
        $sql .= "(" . implode(',', array_keys($insert)) . ") VALUES ";
        $sql .= "(" . implode(',', array_values($insert)) . "),";
        foreach ($data as $key => $val) {
            if ($key == 0) {
                continue;
            }
            $insert = $this->dataFormat($this->tableName[0], $val);
            $sql .= "(" . implode(',', array_values($insert)) . "),";
        }
        $sql = rtrim($sql, ',');
        return $this->exec($sql);
    }

    /**
     * 插入数据并返回主键值
     * @param array $data
     * @return int|Builder
     */
    public function insertGetId(array $data)
    {
        if ($this->insert($data)) {
            return $this->links[$this->master]->lastInsertId();
        }
        return 0;
    }

    /**
     * 合并查询
     * @param array $option
     * @return Mysql
     */
    public function union($option): Mysql
    {
        array_push($this->union, ' UNION ' . $option);
        return $this;

    }

    public function count()
    {
        $this->count = true;
        $count = $this->getData(true, false);
        $this->count = false;
        return $count[0]['COUNT(1)'] ?? 0;
    }

    /**
     * 缓存查询
     * @param $expire
     * @return mixed
     */
    public function cache($expire)
    {
        $this->cache = $expire;
        return $this;
    }

    /**
     * 添加sql注释
     * @param $option
     * @return mixed
     */
    public function comment($option)
    {
        $this->comment = "/* " . $option . " */";
        return $this;
    }

    /**
     * 数据库加锁
     * @param bool $option
     * @return mixed
     */
    public function lock($option = true)
    {
        $this->lock = " FOR UPDATE";
        if ($option !== true) {
            $this->lock = $option;
        }
        return $this;
    }

    /**
     * @param bool $option
     * @return Builder
     */
    public function distinct($option = true): Builder
    {
        $this->distinct = "DISTINCT";
        return $this;
    }

    /**
     * 字段自增
     * @param $filed
     * @param int $number
     * @return int
     */
    public function setInc($filed, $number = 1): int
    {
        $valStr = '`' . $filed . '`=`' . $filed . '`+' . $number;
        return $this->updateSql($valStr);
    }

    /**
     * 字段自减
     * @param $filed
     * @param int $number
     * @return int
     */
    public function setDec($filed, $number = 1): int
    {
        $valStr = '`' . $filed . '`=`' . $filed . '`-' . $number;
        return $this->updateSql($valStr);
    }


    /**
     * 启动事务
     * @return Mysql
     */
    public function startTrans()
    {
        $this->master = true;
        $this->readMaster(true);
        if ($this->trans == 0) {
            $this->links[$this->master]->beginTransaction();
        }
        $this->trans++;
        return $this;
    }

    /**
     * 用于非自动提交状态下面的查询提交
     * @return bool
     */
    public function commit()
    {
        $result = true;
        if ($this->trans > 0) {
            $result = $this->links[$this->master]->commit();
            $this->trans = 0;
        }
        return $result;
    }

    /**
     * 事务回滚
     * @return bool
     */
    public function rollback()
    {
        $result = true;
        if ($this->trans > 0) {
            $result = $this->links[$this->master]->rollback();
            $this->trans = 0;
        }
        return $result;
    }

    /**
     * 关闭连接
     * PHP 在脚本结束时会自动关闭连接。
     */
    public function close()
    {
        $this->resetParam();
        $this->links = [];
        $this->option = [];
    }


    public function __destruct()
    {
        $this->close();
    }
}
